package com.hl.mapper;

import com.hl.pojo.*;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface ConsultantMapper {
    // 添加顾问
    @Insert("insert into consultant (consultant_name, consultant_level_id, avatar, address, personal_introduction) values (#{consultantName}, #{consultantLevelId}, #{avatar}, #{address}, #{personalIntroduction})")
    public Integer add(Consultant consultant);

    // 查询所有顾问
    @Select("select c.*,c.id id1 from consultant c where del = 0")
    @Results({
            @Result(property = "consultantExcellences",column = "id1",many = @Many(select = "a1")),
            @Result(property = "consultantLevel",column = "consultant_level_id",one = @One(select = "a3")),
            @Result(property = "feeStandards",column = "id1" ,many = @Many(select = "b1")),
            @Result(property = "courses",column = "id1" ,many = @Many(select = "c1")),
            @Result(property = "userReviews",column = "id1" ,many = @Many(select = "d1")),
            @Result(property = "answerLeaveWords",column = "id1" ,many = @Many(select = "e1")),
    })
    public List<Consultant> findAll();

    @Select("select *,consultant.id uid from consultant where del = 0 and consultant_name like concat('%',#{name},'%')")
    @Results({
            @Result(property = "consultantExcellences",column = "uid",many = @Many(select = "a1")),
            @Result(property = "consultantLevel",column = "consultant_level_id",one = @One(select = "a3"))
    })
    public List<Consultant> findByName(String name);

    @Select("select * from consultant_excellence where consultant_id = #{id} and del = 0")
    @Results({
            @Result(property = "fieldOfExcellence",column = "excellence_id",
            one = @One(select = "a2"))
    })
    public List<ConsultantExcellence> a1(Integer id);

    @Select("select * from field_of_excellence where id = #{id} and del = 0")
    public FieldOfExcellence a2(Integer id);

    @Select("select * from consultant_level where id = #{id} and del = 0")
    public ConsultantLevel a3(Integer id);

    @Select("select * from fee_standards where consultant_id = #{id} and del = 0")
    public List<FeeStandards> b1(Integer id);
    @Select("select c.image,c.course_name from course c where consultant_id = #{id} and del = 0")
    public List<Course> c1(Integer id);
    @Select("select r.review_content,r.xing,r.review_time,u.username from user_review r join system_user u on r.user_id = u.id where r.consultant_id = #{id} and r.del = 0")
    public List<UserReview> d1(Integer id);
    @Select("select lw.leave_word,lw.answer from answer_leave_word lw where consultant_id = #{id} and del = 0")
    public List<AnswerLeaveWord> e1(Integer id);

    // 根据id删除顾问
    @Update("update consultant set del = 1 where id = #{id}")
    public Integer del(Integer id);

    // 根据id查询顾问
    @Select("select c.*,c.id id1 from consultant c where id = #{id}")
    @Results({
            @Result(property = "consultantExcellences",column = "id1",many = @Many(select = "a1")),
            @Result(property = "consultantLevel",column = "consultant_level_id",one = @One(select = "a3")),
            @Result(property = "feeStandards",column = "id1" ,many = @Many(select = "b1")),
            @Result(property = "courses",column = "id1" ,many = @Many(select = "c1")),
            @Result(property = "userReviews",column = "id1" ,many = @Many(select = "d1")),
            @Result(property = "answerLeaveWords",column = "id1" ,many = @Many(select = "e1")),
    })
    public Consultant findById(Integer id);

    // 更新顾问信息
    @Update("update consultant set consultant_name=#{consultantName},consultant_level_id=#{consultantLevelId}, avatar=#{avatar}, address=#{address}, personal_introduction=#{personalIntroduction}, del=0 where id=#{id}")
    public Integer update(Consultant consultant);

    //查询咨询师根据擅长领域中的一个标签和该咨询师的等级
    @Select("SELECT DISTINCT c.* FROM consultant c JOIN consultant_excellence ce  JOIN consultant_level cl JOIN field_of_excellence fe WHERE  fe.id = ce.excellence_id  and  c.del = 0 and c.consultant_level_id = cl.id  and cl.id= #{consultantLevelId}  and ce.consultant_id = c.id and ce.consultant_id IN( SELECT  ce.consultant_id  FROM field_of_excellence fe JOIN consultant_excellence ce WHERE fe.id = ce.excellence_id and fe.id = #{fieldNameId}) ")
    @Results({
            @Result(property = "consultantExcellences",column = "id",many = @Many(select = "a1")),
            @Result(property = "fieldOfExcellence",column = "excellence_id",
                    one = @One(select = "a2")),
            @Result(property = "consultantLevel",column = "consultant_level_id",one = @One(select = "a3"))
    })
    public List<Consultant> findByExcellenceAndLevel(@Param("consultantLevelId") Integer consultantLevelId,@Param("fieldNameId") Integer fieldNameId);




}